Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Chapter 30
Using Hints

The Oracle optimizer is very efficient and works quite well to produce the best execution plan for your SQL statements based on the information it has to work with. The optimizer does not, however, have the amount of information about your database and your data as you do. This is why Oracle allows you to use hints to tell the optimizer what kind of operations will be more efficient based on knowledge you have about your database and your data.

By using hints, you can tell the optimizer such things as these:

  The best optimization approach for a particular SQL statement
  The goal of the cost-based approach for an SQL statement
  The access path for a statement
  When table scans are more efficient than indexes
  The join order for a statement
  A join operation in a join statement
  The degree of parallelism in a parallel query statement

By using hints, you can use specific information that you know about your data and database to further enhance the performance on certain SQL statements. With hints, you can enhance specific operations that might otherwise be inefficient. Here are some examples of conditions in which hints may significantly improve performance:

  Indexed columns with a large number of duplicate values. Telling the optimizer to bypass the index when the value is one you know has a large number of duplicates is more efficient than letting the optimizer use the index.
  Table access that performs a large table scan. By specifying a larger number of parallel query servers, you can improve performance.
  Table access that performs a small table scan. If you know that the amount of data to be scanned is small, you may want to disable the parallel query option for this particular operation.

These are just a few of the exceptional conditions in which the default optimization may not be efficient. The information you know about your data and application can be used to make more efficient optimization choices.

This chapter looks at the different optimization hints available and when you can use them to improve the optimization of your SQL statements. The chapter first explains how hints are implemented within your SQL statements and then describes the hints themselves, categorized by function.

Implementing Hints

Hints are implemented by enclosing them within a comment to the SQL statement. An SQL statement can have only one comment containing hints; these hints can follow only the SELECT, UPDATE, or DELETE keyword. Each hint applies only to the statement block in which the hint appears.

Hint Syntax

If you have a compound query in which several SELECTs are combined with the UNION operator, each query must contain its own hint. The hint applied to one of the SELECT statements does not carry over to any other SELECT. Oracle comments can have two forms:

/* comment */ The /* indicates the beginning of the comment; the */ indicates the end of a comment. The comment can be multiple lines long.
-- comment The comment consists of the remainder of the line following the -- characters.

Adding the + character following the beginning of the comment indicates to the parser that the following text is a hint to the optimizer so that it is parsed as such (refer to the example in the following section).

Hint Errors

If a hint is incorrectly specified, Oracle ignores it and does not return an error. The following conditions cause hints to be ignored:

  Comment does not follow a SELECT, UPDATE, or DELETE statement. If the comment containing the hint does not follow one of these statements, the hint is ignored.
  Syntax errors. A hint containing syntax errors is ignored. Other hints within the same comment are evaluated individually.
  Conflicting hints. Conflicting hints (for example, FULL and INDEX) are ignored, but other nonconflicting hints within the same comment are evaluated individually.
Some Examples of Hints

Here is an example of how a hint in an SQL statement looks:

SELECT /*+ CHOOSE */ account_no, name, balance FROM bank_account WHERE
account_no = '12631';

This hint specifies that the optimizer should use the CHOOSE option, overriding any other optimization mode that might be set.

Here is another example of a hint (notice that the statement is the same; to use the -- form of the comment, the comment and hint must be at the end of the line):

SELECT --+ CHOOSE
     account_no, name, balance
FROM
    bank_account
WHERE
    account_no = '12631';

The parser and optimizer consider both of these methods for indicating hints to be equivalent.

Using Multiple Hints

Except in the case of conflicting hints, it is possible to merge multiple hints together. In some cases, merging hints can be very useful and possibly essential.

In many parallel query hints, it is not uncommon for the FULL (full table scan) hint to be used in conjunction with the PARALLEL (parallel query) hint. By using these two hints together, you ensure that a full-table scan will be executed and that the parallel-table scan feature of the Parallel Query option will be used.

To use multiple hints together, simply add additional hints within the same comment, as in this example that combines the FULL and PARALLEL hints:

SELECT /* FULL( dogs ) PARALLEL ( dogs, 5 ) */
     dogname, age, owner
FROM
     dogs
WHERE
     age < 5;

Hints

The following sections describe the hints available to the Oracle optimizer. The hints are grouped into several categories based on function.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.